﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RMH.DataAccess.AdoNet.ReportEntities;

namespace RMH.DataAccess.AdoNet
{
    public class ReportDataHelper
    {
        public static List<LengthOfStayMonthSummary> GetLengthOfStayReport(DateTime? startDate, DateTime? endDate)
        {
            System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();

            List<LengthOfStayMonthSummary> results = new List<LengthOfStayMonthSummary>();

            using(SqlConnection conn = new SqlConnection(AdoDataHelper.GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand("report_LengthOfStay", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                
                SqlParameter pStartDate = new SqlParameter("@startDate", SqlDbType.DateTime) {Value = startDate};
                SqlParameter pEndDate = new SqlParameter("@endDate", SqlDbType.DateTime) { Value = endDate };
                cmd.Parameters.Add(pStartDate);
                cmd.Parameters.Add(pEndDate);
                
                conn.Open();
                using(SqlDataReader reader = cmd.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        LengthOfStayMonthSummary monthSummary = new LengthOfStayMonthSummary();
                        monthSummary.CheckinMonth = reader.GetInt32(0);
                        // SQL DATE functions use "1" for January, DateTimeFormatInfo uses "0" for January
                        monthSummary.CheckinMonthName = dateInfo.MonthNames[monthSummary.CheckinMonth-1];
                        monthSummary.CheckinYear = reader.GetInt32(1);
                        if (!reader.IsDBNull(2))
                        {
                            monthSummary.AverageStayHours = reader.GetInt32(2);
                        }
                        if(!reader.IsDBNull(3))
                        {
                            monthSummary.ShortestStayHours = reader.GetInt32(3);
                        }
                        if(!reader.IsDBNull(4))
                        {
                            monthSummary.LongestStayHours = reader.GetInt32(4);
                        }
                        results.Add(monthSummary);
                    }
                }
                conn.Close();
            }
            return results;
        }
    }
}
